------------------------------------------------------------------------
-- GENERAL SQL FILE FOR ANY APEX APPLICATION (PARENT–CHILD–MATERIAL MODEL)
-- Includes schema, constraints, trigger, procedures, sample queries,
-- MODEL clause, window functions, validation regex (commented),
-- reports, charts, and reusable components.
------------------------------------------------------------------------

-------------------------
-- 1. DROP OLD TABLES 
-------------------------
BEGIN EXECUTE IMMEDIATE 'DROP TABLE audit_log PURGE';        EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE children PURGE';         EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE parents PURGE';          EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE materials PURGE';        EXCEPTION WHEN OTHERS THEN NULL; END;
/

-------------------------
-- 2. SCHEMA CREATION
-------------------------

-- PARENT TABLE
CREATE TABLE parents (
  parent_id        NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  parent_name      VARCHAR2(100) NOT NULL,
  age              NUMBER CHECK (age BETWEEN 18 AND 120),
  gender           VARCHAR2(20) CHECK (gender IN ('Male','Female','Female','Other')),
  mobile           VARCHAR2(20) UNIQUE,
  email            VARCHAR2(200),
  num_children     NUMBER DEFAULT 0 CHECK (num_children >= 0),
  consent_signed   CHAR(1) DEFAULT 'N' CHECK (consent_signed IN ('Y','N')),
  created_at       TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- CHILD TABLE
CREATE TABLE children (
  child_id         NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  parent_id        NUMBER NOT NULL,
  child_name       VARCHAR2(100) NOT NULL,
  child_age        NUMBER CHECK (child_age BETWEEN 0 AND 18),
  child_category   VARCHAR2(100),
  created_at       TIMESTAMP DEFAULT SYSTIMESTAMP,
  CONSTRAINT fk_children_parent FOREIGN KEY (parent_id)
    REFERENCES parents(parent_id) ON DELETE CASCADE
);

-- MATERIAL TABLE
CREATE TABLE materials (
  material_id        NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  material_name      VARCHAR2(100) NOT NULL,
  texture            VARCHAR2(50),
  color              VARCHAR2(50),
  source             VARCHAR2(50) CHECK (source IN ('Organic','Recycled','Synthetic','Natural','Mixed')),
  certificate        VARCHAR2(100),
  non_toxic          CHAR(1) DEFAULT 'Y' CHECK (non_toxic IN ('Y','N')),
  created_at         TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- AUDIT LOG TABLE (GENERIC)
CREATE TABLE audit_log (
  audit_id       NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  ref_id         NUMBER,
  ref_type       VARCHAR2(30),
  details        VARCHAR2(4000),
  created_at     TIMESTAMP DEFAULT SYSTIMESTAMP
);

-------------------------
-- 3. TRIGGER: AUDIT + CONSENT VALIDATION
-------------------------
CREATE OR REPLACE TRIGGER trg_child_after_insert
AFTER INSERT ON children
FOR EACH ROW
DECLARE
  v_consent CHAR(1);
BEGIN
  -- Check parent consent
  SELECT consent_signed INTO v_consent
  FROM parents
  WHERE parent_id = :NEW.parent_id;

  IF v_consent <> 'Y' THEN
    RAISE_APPLICATION_ERROR(-20001, 'Consent must be signed before adding child.');
  END IF;

  -- Insert into audit log
  INSERT INTO audit_log (ref_id, ref_type, details)
  VALUES (
    :NEW.child_id,
    'CHILD',
    'Child added: ' || :NEW.child_name ||
    ', Age: ' || :NEW.child_age
  );
END;
/
SHOW ERRORS;

-------------------------
-- 4. PROCEDURE: UPDATE CHILD COUNT
-------------------------
CREATE OR REPLACE PROCEDURE pr_update_child_count(p_parent_id NUMBER) IS
  v_total NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_total FROM children WHERE parent_id = p_parent_id;
  UPDATE parents SET num_children = v_total WHERE parent_id = p_parent_id;
END;
/
SHOW ERRORS;

-------------------------
-- 5. WINDOW FUNCTION QUERIES (GENERAL USE)
-------------------------

-- RANK PARENTS BY CHILD COUNT
SELECT parent_id, parent_name, num_children,
       RANK() OVER (ORDER BY num_children DESC) AS rank_no
FROM parents;

-- CHILD ROW NUMBER + LAG + LEAD
SELECT parent_id, child_name, child_age,
       ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY child_age) AS rn,
       LAG(child_age) OVER (PARTITION BY parent_id ORDER BY child_age) AS prev_age,
       LEAD(child_age) OVER (PARTITION BY parent_id ORDER BY child_age) AS next_age
FROM children;

-- AVG AGE PER PARENT
SELECT parent_id,
       AVG(child_age) OVER (PARTITION BY parent_id) AS avg_age,
       COUNT(*) OVER (PARTITION BY parent_id) AS total_children
FROM children;

-------------------------
-- 6. MODEL CLAUSE (GENERAL AXIS)
-------------------------
WITH age_counts AS (
  SELECT child_age AS age, COUNT(*) cnt
  FROM children
  GROUP BY child_age
)
SELECT age, cnt
FROM (
  SELECT LEVEL - 1 AS age, 0 AS cnt
  FROM dual CONNECT BY LEVEL <= 19
)
MODEL
  DIMENSION BY (age)
  MEASURES (cnt)
  RULES (
    cnt[ANY] = NVL(
      (SELECT ac.cnt FROM age_counts ac WHERE ac.age = age), 0
    )
  )
ORDER BY age;

-------------------------
-- 7. GENERAL REPORT QUERIES
-------------------------

-- Parents Report
SELECT parent_id, parent_name, gender, age, mobile, consent_signed
FROM parents
ORDER BY parent_name;

-- Children Report
SELECT c.child_id, c.child_name, c.child_age, c.child_category,
       p.parent_name
FROM children c
JOIN parents p ON c.parent_id = p.parent_id
ORDER BY c.child_age, c.child_name;

-- Materials Report
SELECT material_id, material_name, texture, color, source, certificate, non_toxic
FROM materials
ORDER BY material_name;

-- Audit Log Report
SELECT * FROM audit_log ORDER BY created_at DESC;

-------------------------
-- 8. GENERAL CHART QUERIES
-------------------------

-- Chart 1: Category Count
SELECT child_category AS label, COUNT(*) AS value
FROM children
GROUP BY child_category;

-- Chart 2: Gender Distribution
SELECT gender AS label, COUNT(*) AS value
FROM parents
GROUP BY gender;

-- Chart 3: Age Distribution
SELECT child_age AS label, COUNT(*) AS value
FROM children
GROUP BY child_age ORDER BY child_age;

-------------------------
-- 9. REGEX VALIDATION (FOR APEX)
-------------------------
-- Name:
-- ^[A-Za-z][A-Za-z .'-]{1,99}$

-- Mobile (International):
-- ^(\+?\d{1,3}[- ]?)?\d{10}$

-- Email:
-- ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$

-- DOB (YYYY-MM-DD):
-- ^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$

-- Age:
-- ^[0-9]{1,3}$

-------------------------
-- 10. SAMPLE SELECTS
-------------------------
SELECT COUNT(*) AS total_parents FROM parents;
SELECT COUNT(*) AS total_children FROM children;
SELECT COUNT(*) AS organic_materials FROM materials WHERE source='Organic';

------------------------------------------------------------------------
-- END OF GENERAL SQL FILE
------------------------------------------------------------------------
